package com.b2c.repository.erp;

import com.b2c.entity.result.PagingResponse;
import com.b2c.repository.Tables;
import com.b2c.entity.enums.erp.EnumGoodsStockLogSourceType;
import com.b2c.entity.enums.erp.EnumGoodsStockLogType;
import com.b2c.entity.vo.ErpGoodsStockLogsListVo;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import java.util.ArrayList;
import java.util.List;

/**
 * 描述：
 *
 * @author qlp
 * @date 2019-10-08 17:02
 */
@Repository
public class ErpGoodsStockLogsRepository {
    @Autowired
    private JdbcTemplate jdbcTemplate;

//    public List<ErpGoodsStockLogsListVo> getListBySource(EnumGoodsStockLogSourceType type, Long sourceId) {
//        String sql = "SELECT sl.*,g.name as goodsName,gs.color_value,gs.size_value,lo.number as locationNumber " +
//                " FROM " + Tables.ErpGoodsStockLogs + " sl " +
//                " LEFT JOIN " + Tables.ErpGoods + " g  on g.id=sl.goodsId " +
//                " LEFT JOIN " + Tables.ErpGoodsSpec + " gs  on gs.id=sl.specId " +
//                " LEFT JOIN " + Tables.ErpStockLocation + " lo  on lo.id=sl.locationId " +
//                " WHERE " +
//                "sl.sourceType=? AND sl.sourceId=? ";
//        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ErpGoodsStockLogsListVo.class), type.getIndex(), sourceId);
//    }

    /**
     * 仓库日志 （仓库系统使用）
     *
     * @param type      类型 入库 、出库
     * @param pageIndex
     * @param pageSize
     * @return
     */
    @Transactional
    public PagingResponse<ErpGoodsStockLogsListVo> getListByType(EnumGoodsStockLogType type, Integer pageIndex, Integer pageSize, Integer startTime, Integer endTime) {
        String sql = "SELECT SQL_CALC_FOUND_ROWS sl.*,g.name as goodsName,gs.color_value,gs.size_value,lo.number as locationNumber " +
                " FROM " + Tables.ErpGoodsStockLogs + " sl " +
                " LEFT JOIN " + Tables.ErpGoods + " g  on g.id=sl.goodsId " +
                " LEFT JOIN " + Tables.ErpGoodsSpec + " gs  on gs.id=sl.specId " +
                " LEFT JOIN " + Tables.ErpStockLocation + " lo  on lo.id=sl.locationId " +
                " WHERE sl.type=? ";

        List<Object> params = new ArrayList<>();
        params.add(type.getIndex());

        if (startTime != null && startTime > 0) {
            sql += " AND sl.createOn >= ?";
            params.add(startTime);
        }
        if (endTime != null && endTime > 0) {
            sql += " AND sl.createOn <= ?";
            params.add(endTime);
        }

        sql += " ORDER BY sl.id desc  LIMIT ?,? ";
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);

        var list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ErpGoodsStockLogsListVo.class), params.toArray(new Object[params.size()]));
        return new PagingResponse<>(pageIndex, pageSize, getTotalSize(), list);
    }

    /**
     * @param type
     * @param sourceType
     * @param pageIndex
     * @param pageSize
     * @return
     */
    @Transactional
    public PagingResponse<ErpGoodsStockLogsListVo> getListByTypeAndSource(EnumGoodsStockLogType type, EnumGoodsStockLogSourceType sourceType, Integer pageIndex, Integer pageSize) {
        String sql = "SELECT SQL_CALC_FOUND_ROWS sl.*,g.name as goodsName,gs.color_value,gs.size_value,lo.number as locationNumber " +
                " FROM " + Tables.ErpGoodsStockLogs + " sl " +
                " LEFT JOIN " + Tables.ErpGoods + " g  on g.id=sl.goodsId " +
                " LEFT JOIN " + Tables.ErpGoodsSpec + " gs  on gs.id=sl.specId " +
                " LEFT JOIN " + Tables.ErpStockLocation + " lo  on lo.id=sl.locationId " +
                " WHERE " +
                "sl.type=? AND sl.sourceType=? ORDER BY sl.id desc  LIMIT ?,? ";

        List<Object> params = new ArrayList<>();
        params.add(type.getIndex());
        params.add(sourceType.getIndex());
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);

        var list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ErpGoodsStockLogsListVo.class), params.toArray(new Object[params.size()]));
        return new PagingResponse<>(pageIndex, pageSize, getTotalSize(), list);
    }

    private String getStockInOutSQL() {
        String sql = "SELECT SQL_CALC_FOUND_ROWS sl.id,sl.quantity,sl.type,sl.sourceType,sl.remark,sl.createTime,sl.createOn" +
                ",g.number as goodsNumber,g.name as goodsName,gs.color_value,gs.size_value,gs.specNumber,lo.number as locationNumber " +
                " FROM " + Tables.ErpGoodsStockLogs + " sl " +
                " LEFT JOIN " + Tables.ErpGoods + " g  on g.id=sl.goodsId " +
                " LEFT JOIN " + Tables.ErpGoodsSpec + " gs  on gs.id=sl.specId " +
                " LEFT JOIN " + Tables.ErpStockLocation + " lo  on lo.id=sl.locationId " +
                " WHERE sl.type=? ";
        return sql;
    }

    /**
     * 仓库入库
     *
     * @param pageIndex
     * @param pageSize
     * @param sourceType  EnumGoodsStockLogSourceType
     * @param goodsNumber
     * @param specNumber
     * @return
     */
    @Transactional
    public PagingResponse<ErpGoodsStockLogsListVo> getStockInListBySourceType(Integer pageIndex, Integer pageSize, EnumGoodsStockLogSourceType sourceType, String goodsNumber, String specNumber) {
        String sql = getStockInOutSQL();

        List<Object> params = new ArrayList<>();
        params.add(EnumGoodsStockLogType.IN.getIndex());
        if (sourceType != null) {
            sql += " AND sl.sourceType=? ";
            params.add(sourceType.getIndex());
        }
        if (StringUtils.isEmpty(goodsNumber) == false) {
            sql += " AND g.number = ? ";
            params.add(goodsNumber);
        }
        if (StringUtils.isEmpty(specNumber) == false) {
            sql += " AND gs.specNumber = ? ";
            params.add(specNumber);
        }

        sql += " ORDER BY sl.id desc  LIMIT ?,? ";
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);

        var list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ErpGoodsStockLogsListVo.class), params.toArray(new Object[params.size()]));
        return new PagingResponse<>(pageIndex, pageSize, getTotalSize(), list);
    }

    public List<ErpGoodsStockLogsListVo> getStockInListBySourceTypeForExcel(EnumGoodsStockLogSourceType sourceType, String goodsNumber, String specNumber) {
        String sql = getStockInOutSQL();

        List<Object> params = new ArrayList<>();
        params.add(EnumGoodsStockLogType.IN.getIndex());
        if (sourceType != null) {
            sql += " AND sl.sourceType=? ";
            params.add(sourceType.getIndex());
        }
        if (StringUtils.isEmpty(goodsNumber) == false) {
            sql += " AND g.number = ? ";
            params.add(goodsNumber);
        }
        if (StringUtils.isEmpty(specNumber) == false) {
            sql += " AND gs.specNumber = ? ";
            params.add(specNumber);
        }

        sql += " ORDER BY sl.id desc   ";


        var list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ErpGoodsStockLogsListVo.class), params.toArray(new Object[params.size()]));
        return list;
    }

    @Transactional
    public PagingResponse<ErpGoodsStockLogsListVo> getStockOutListBySourceType(Integer pageIndex, Integer pageSize, EnumGoodsStockLogSourceType sourceType, String goodsNumber, String specNumber) {
        String sql = getStockInOutSQL();

        List<Object> params = new ArrayList<>();
        params.add(EnumGoodsStockLogType.OUT.getIndex());
        if (sourceType != null) {
            sql += " AND sl.sourceType=? ";
            params.add(sourceType.getIndex());
        }
        if (StringUtils.isEmpty(goodsNumber) == false) {
            sql += " AND g.number = ? ";
            params.add(goodsNumber);
        }
        if (StringUtils.isEmpty(specNumber) == false) {
            sql += " AND gs.specNumber = ? ";
            params.add(specNumber);
        }

        sql += " ORDER BY sl.id desc  LIMIT ?,? ";
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);

        var list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ErpGoodsStockLogsListVo.class), params.toArray(new Object[params.size()]));
        return new PagingResponse<>(pageIndex, pageSize, getTotalSize(), list);
    }

    public List<ErpGoodsStockLogsListVo> getStockOutListBySourceTypeForExcel(EnumGoodsStockLogSourceType sourceType, String goodsNumber, String specNumber) {
        String sql = getStockInOutSQL();

        List<Object> params = new ArrayList<>();
        params.add(EnumGoodsStockLogType.OUT.getIndex());
        if (sourceType != null) {
            sql += " AND sl.sourceType=? ";
            params.add(sourceType.getIndex());
        }
        if (StringUtils.isEmpty(goodsNumber) == false) {
            sql += " AND g.number = ? ";
            params.add(goodsNumber);
        }
        if (StringUtils.isEmpty(specNumber) == false) {
            sql += " AND gs.specNumber = ? ";
            params.add(specNumber);
        }

        sql += " ORDER BY sl.id desc   ";


        var list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ErpGoodsStockLogsListVo.class), params.toArray(new Object[params.size()]));
        return list;
    }

    /**
     * 查询总页数
     *
     * @return
     */
    protected int getTotalSize() {
        return jdbcTemplate.queryForObject("SELECT FOUND_ROWS() as row_num;", int.class);
    }

}
